package com.big.codegen.util;

import com.big.codegen.domain.dto.ColumnDTO;
import com.big.codegen.domain.dto.DatasourceDTO;
import com.big.common.core.exception.CustomerException;
import lombok.extern.slf4j.Slf4j;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

/**
 * 数据库工具类
 * 支持多种数据库类型的连接和操作
 *
 * @author 山归岿
 * @create 2025-06-26-19:30
 */
@Slf4j
public class DatabaseUtils {

    /**
     * 数据库类型枚举
     */
    public enum DatabaseType {
        MYSQL("mysql", "com.mysql.cj.jdbc.Driver", "SELECT 1"),
        POSTGRESQL("postgresql", "org.postgresql.Driver", "SELECT 1"),
        ORACLE("oracle", "oracle.jdbc.driver.OracleDriver", "SELECT 1 FROM DUAL"),
        SQLSERVER("sqlserver", "com.microsoft.sqlserver.jdbc.SQLServerDriver", "SELECT 1"),
        H2("h2", "org.h2.Driver", "SELECT 1");

        private final String type;
        private final String driverClass;
        private final String testSql;

        DatabaseType(String type, String driverClass, String testSql) {
            this.type = type;
            this.driverClass = driverClass;
            this.testSql = testSql;
        }

        public String getType() {
            return type;
        }

        public String getDriverClass() {
            return driverClass;
        }

        public String getTestSql() {
            return testSql;
        }

        public static DatabaseType fromType(String type) {
            for (DatabaseType dbType : values()) {
                if (dbType.getType().equalsIgnoreCase(type)) {
                    return dbType;
                }
            }
            throw new CustomerException("不支持的数据库类型: " + type);
        }
    }

    /**
     * 获取数据库连接
     *
     * @param datasource 数据源配置
     * @return 数据库连接
     */
    public static Connection getConnection(DatasourceDTO datasource) {
        try {
            DatabaseType dbType = DatabaseType.fromType(datasource.getDbType());
            Class.forName(dbType.getDriverClass());
            
            Properties props = new Properties();
            props.setProperty("user", datasource.getUsername());
            props.setProperty("password", datasource.getPassword());
            props.setProperty("useSSL", "false");
            props.setProperty("allowPublicKeyRetrieval", "true");
            props.setProperty("serverTimezone", "Asia/Shanghai");
            
            return DriverManager.getConnection(datasource.getUrl(), props);
        } catch (Exception e) {
            log.error("获取数据库连接失败: {}", e.getMessage());
            throw new CustomerException("数据库连接失败: " + e.getMessage());
        }
    }

    /**
     * 测试数据库连接
     *
     * @param datasource 数据源配置
     * @return 连接是否成功
     */
    public static boolean testConnection(DatasourceDTO datasource) {
        try (Connection connection = getConnection(datasource)) {
            DatabaseType dbType = DatabaseType.fromType(datasource.getDbType());
            try (PreparedStatement stmt = connection.prepareStatement(dbType.getTestSql());
                 ResultSet rs = stmt.executeQuery()) {
                return rs.next();
            }
        } catch (Exception e) {
            log.error("测试数据库连接失败: {}", e.getMessage());
            return false;
        }
    }

    /**
     * 获取数据库中所有表名
     *
     * @param datasource 数据源配置
     * @return 表名列表
     */
    public static List<String> getTableNames(DatasourceDTO datasource) {
        List<String> tableNames = new ArrayList<>();
        try (Connection connection = getConnection(datasource)) {
            DatabaseMetaData metaData = connection.getMetaData();
            try (ResultSet rs = metaData.getTables(datasource.getDatabaseName(), null, null, new String[]{"TABLE"})) {
                while (rs.next()) {
                    tableNames.add(rs.getString("TABLE_NAME"));
                }
            }
        } catch (Exception e) {
            log.error("获取表名列表失败: {}", e.getMessage());
            throw new CustomerException("获取表名列表失败: " + e.getMessage());
        }
        return tableNames;
    }

    /**
     * 获取表的字段信息
     *
     * @param datasource 数据源配置
     * @param tableName  表名
     * @return 字段信息列表
     */
    public static List<ColumnDTO> getTableColumns(DatasourceDTO datasource, String tableName) {
        List<ColumnDTO> columns = new ArrayList<>();
        try (Connection connection = getConnection(datasource)) {
            DatabaseMetaData metaData = connection.getMetaData();
            
            // 获取主键信息
            List<String> primaryKeys = new ArrayList<>();
            try (ResultSet pkRs = metaData.getPrimaryKeys(datasource.getDatabaseName(), null, tableName)) {
                while (pkRs.next()) {
                    primaryKeys.add(pkRs.getString("COLUMN_NAME"));
                }
            }
            
            // 获取字段信息
            try (ResultSet rs = metaData.getColumns(datasource.getDatabaseName(), null, tableName, null)) {
                while (rs.next()) {
                    ColumnDTO column = ColumnDTO.builder()
                            .columnName(rs.getString("COLUMN_NAME"))
                            .columnComment(rs.getString("REMARKS"))
                            .columnType(rs.getString("TYPE_NAME"))
                            .columnLength(rs.getInt("COLUMN_SIZE"))
                            .columnPrecision(rs.getInt("DECIMAL_DIGITS"))
                            .isNullable("YES".equals(rs.getString("IS_NULLABLE")))
                            .isPrimaryKey(primaryKeys.contains(rs.getString("COLUMN_NAME")))
                            .isAutoIncrement("YES".equals(rs.getString("IS_AUTOINCREMENT")))
                            .columnDefault(rs.getString("COLUMN_DEF"))
                            .build();
                    columns.add(column);
                }
            }
        } catch (Exception e) {
            log.error("获取表字段信息失败: {}", e.getMessage());
            throw new CustomerException("获取表字段信息失败: " + e.getMessage());
        }
        return columns;
    }

    /**
     * 关闭数据库连接
     *
     * @param connection 数据库连接
     */
    public static void closeConnection(Connection connection) {
        if (connection != null) {
            try {
                connection.close();
            } catch (SQLException e) {
                log.error("关闭数据库连接失败: {}", e.getMessage());
            }
        }
    }
}
